package com.example.qrcodecamera;


import android.app.Activity;
import android.content.Context;
import android.util.Log;
import android.widget.Toast;

import com.example.qrcodecamera.JDBCUtil;

import java.lang.ref.WeakReference;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class UserDao {
    private final WeakReference<Context> contextRef; // 添加 Context 成员变量
    private final SharedPreferencesHelper spHelper;
    // 通过构造函数接收 Context
    public UserDao(Context context) {
        this.contextRef = new WeakReference<>(context);
        this.spHelper = new SharedPreferencesHelper(context);
    }
    // 测试数据库连接
    public void testConnection() {
        new Thread(() -> {
            Connection conn = null;
            try {
                conn = JDBCUtil.getNewConnection(
                        spHelper.getIp(),
                        spHelper.getPort(),
                        spHelper.getUsername(),
                        spHelper.getPassword()
                );
                String version = conn.getMetaData().getDatabaseProductVersion();
                showToast("✅ 连接成功\n版本: " + version, Toast.LENGTH_LONG);
            } catch (Exception e) {
                showToast("❌ 连接失败: " + e.getMessage(), Toast.LENGTH_LONG);
                Log.e("DB_TEST", "连接测试失败", e);
            } finally {
                DbUtils.closeQuietly(conn);
            }
        }).start();
    }


    //=== 私有辅助方法 ===//
    private boolean validateInput(float org,float tare, float net) {
        return org>0 &&tare >= 0 && net >= 0;
    }

    private void handleSaveResult(int affectedRows) {
        String msg = affectedRows > 0 ?
                "✅ 保存成功 (" + affectedRows + " 条)" :
                "⚠️ 数据未变化";
        showToast(msg, Toast.LENGTH_LONG);
    }

    private void handleDatabaseError(SQLException e) {
        String errorMsg = "❌ 保存失败: " + JDBCUtil.parseSqlException(e);
        Log.e("DB_SAVE", "错误码: " + e.getErrorCode(), e);
        showToast(errorMsg, Toast.LENGTH_LONG);
    }

    private void handleSystemError(Exception e) {
        Log.e("DB_SAVE", "系统错误", e);
        showToast("‼️ 系统错误: " + e.getMessage(), Toast.LENGTH_LONG);
    }

    private void showToast(String msg, int duration) {
        Context context = contextRef.get();
        if (context instanceof Activity) {
            ((Activity) context).runOnUiThread(() ->
                    Toast.makeText(context, msg, duration).show()
            );
        }
    }


    public void saveCSVData(List<CSVRecord> records) {
        new Thread(() -> {
            Connection conn = null;
            PreparedStatement stmt = null;

            try {
                conn = JDBCUtil.getNewConnection(
                        spHelper.getIp(),
                        spHelper.getPort(),
                        spHelper.getUsername(),
                        spHelper.getPassword()
                );
                String sql = "INSERT INTO csv_import_data (goods_name, staff_id, operator, batch, material_id, " +
                        "gross_weight, tare_weight, net_weight, timestamp) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

                stmt = conn.prepareStatement(sql);

                // 批量插入参数设置
                for (CSVRecord record : records) {
                    stmt.setString(1, record.getGoodsName());
                    stmt.setString(2, record.getStaffId());
                    stmt.setString(3, record.getOperator());
                    stmt.setString(4, record.getBatch());
                    stmt.setString(5, record.getMaterialId());
                    stmt.setDouble(6, record.getGrossWeight());
                    stmt.setDouble(7, record.getTareWeight());
                    stmt.setDouble(8, record.getNetWeight());
                    stmt.setString(9, record.getTimestamp());
                    stmt.addBatch();
                }

                // 执行批量插入
                int[] results = stmt.executeBatch();
                handleBatchResult(results);

            } catch (SQLException e) {
                handleDatabaseError(e);
            } catch (Exception e) {
                handleSystemError(e);
            } finally {
                DbUtils.closeResources(conn, stmt, null);
            }
        }).start();
    }



    // 新增批量结果处理方法
    private void handleBatchResult(int[] results) {
        int successCount = 0;
        for (int result : results) {
            if (result >= 0) successCount++;
        }

        String msg = "成功插入 " + successCount + "/" + results.length + " 条数据";
        showToast(msg, Toast.LENGTH_LONG);
    }


    // UserDao.java 新增方法
    public void queryByBatchNumber(String material_id, QueryResultCallback callback) {
        new Thread(() -> {
            Connection conn = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;

            try {
                conn = JDBCUtil.getNewConnection(
                        spHelper.getIp(),
                        spHelper.getPort(),
                        spHelper.getUsername(),
                        spHelper.getPassword()
                );
                String sql = "SELECT * FROM csv_import_data WHERE material_id = ? ORDER BY timestamp DESC";
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, material_id);

                rs = stmt.executeQuery();
                List<CSVRecord> records = new ArrayList<>();

                while (rs.next()) {
                    CSVRecord record = new CSVRecord(
                            rs.getInt("id"),
                            rs.getString("goods_name"),
                            rs.getString("staff_id"),
                            rs.getString("operator"),
                            rs.getString("batch"),
                            rs.getString("material_id"),
                            rs.getDouble("gross_weight"),
                            rs.getDouble("tare_weight"),
                            rs.getDouble("net_weight"),
                            rs.getString("timestamp")
                    );
                    records.add(record);
                }

                callback.onSuccess(records);
            } catch (SQLException e) {
                handleDatabaseError(e);
                callback.onFailure("数据库查询失败: " + e.getMessage());
            } catch (Exception e) {
                handleSystemError(e);
                callback.onFailure("系统错误: " + e.getMessage());
            } finally {
                DbUtils.closeResources(conn, stmt, rs);
            }
        }).start();
    }

    // 新增回调接口
    public interface QueryResultCallback {
        void onSuccess(List<CSVRecord> records);
        void onFailure(String errorMessage);
    }

    public interface materialQueryResultCallback {
        void onSuccess1(List<MaterialInfocsv> records);


        void onFailure(String errorMessage);
    }




    // 在UserDao类中添加以下方法
    public void saveMaterialData(List<MaterialCSVRecord> records) {
        new Thread(() -> {
            Connection conn = null;
            PreparedStatement stmt = null;

            try {
                conn = JDBCUtil.getNewConnection(
                        spHelper.getIp(),
                        spHelper.getPort(),
                        spHelper.getUsername(),
                        spHelper.getPassword()
                );
                String sql = "INSERT INTO material (material_id, material_name, total_weight, current_weight, storage_time) " +
                        "VALUES (?, ?, ?, ?, ?)";

                stmt = conn.prepareStatement(sql);

                // 批量插入参数设置
                for (MaterialCSVRecord record : records) {
                    stmt.setString(1, record.getMaterialId());
                    stmt.setString(2, record.getMaterialName());
                    stmt.setDouble(3, record.getTotalWeight());
                    stmt.setDouble(4, record.getCurrentWeight());
                    stmt.setString(5, record.getStorageTime());
                    stmt.addBatch();
                }

                // 执行批量插入
                int[] results = stmt.executeBatch();
                handleMaterialBatchResult(results, records.size());

            } catch (SQLException e) {
                handleDatabaseError(e);
            } catch (Exception e) {
                handleSystemError(e);
            } finally {
                DbUtils.closeResources(conn, stmt, null);
            }
        }).start();
    }

    private void handleMaterialBatchResult(int[] results, int total) {
        int successCount = 0;
        for (int result : results) {
            if (result >= 0) successCount++;
        }

        String msg = "成功插入 " + successCount + "/" + total + " 条物料数据";
        showToast(msg, Toast.LENGTH_LONG);
    }

    public void materialqueryByBatchNumber(String material_id,materialQueryResultCallback callback) {
        new Thread(() -> {
            Connection conn = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;

            try {
                conn = JDBCUtil.getNewConnection(
                        spHelper.getIp(),
                        spHelper.getPort(),
                        spHelper.getUsername(),
                        spHelper.getPassword()
                );
                String sql =  "SELECT storage_time, material_id, material_name,total_weight, current_weight " +
                        "FROM material WHERE material_id = ? ORDER BY storage_time DESC";;
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, material_id);

                rs = stmt.executeQuery();
                List<MaterialInfocsv> records = new ArrayList<>();

                while (rs.next()) {
                    MaterialInfocsv record = new MaterialInfocsv(
                            rs.getString("storage_time"),    // 入库时间
                            rs.getString("material_id"),     // 物料编号
                            rs.getString("material_name"),   // 物料名称
                            rs.getString("total_weight"),    // 总重量
                            rs.getString("current_weight")       // 现存重量
                    );
                    records.add(record);
                }

                callback.onSuccess1(records);
            } catch (SQLException e) {
                handleDatabaseError(e);
                callback.onFailure("数据库查询失败: " + e.getMessage());
            } catch (Exception e) {
                handleSystemError(e);
                callback.onFailure("系统错误: " + e.getMessage());
            } finally {
                DbUtils.closeResources(conn, stmt, rs);
            }
        }).start();
    }

}
